if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_delete')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_delete
go


create procedure bd_proc_schedule_delete (@user_serial_str varchar(max),@year_month_str varchar(max),@ip varchar(200),@operator varchar(200)) as
BEGIN

	CREATE TABLE #user_serial(
		id INT IDENTITY,
		user_serial VARCHAR(50) COLLATE Chinese_PRC_CI_AS
	)

	CREATE TABLE #year_month(
		id INT IDENTITY,
		year_month VARCHAR(20) COLLATE Chinese_PRC_CI_AS
	)

	CREATE TABLE #data(
		id INT IDENTITY,
		user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
		user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
		year_month varchar(20) COLLATE Chinese_PRC_CI_AS
	)

	--创建 返回不满足排班的人的原因
	create table #return_table(
		user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
		user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
		user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
		dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
		error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
	)


--  异常扑捉机制
	BEGIN TRY


	-- 开启事务
		BEGIN TRAN

			-- 拆分用户编号
			insert into #user_serial
			select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');

			-- 拆分年月
			insert into #year_month
			select * from string_split(iif(isnull(@year_month_str,'')='','',@year_month_str),',');

			-- 插入数据表
			insert into #data(user_serial,year_month)
			select a.user_serial,b.year_month
			from #user_serial a
			inner join #year_month b on a.id = b.id


			--判断删除的排班是否有封账
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select  d.user_serial,u.user_no,u.user_lname,dep.dep_name,'删除的排班包含封账数据，无法删除' from  #data d
			inner join bd_kq_accounts c on d.user_serial = c.user_serial and SUBSTRING(d.year_month, 1, 4) = c.year and SUBSTRING(d.year_month, 6, 2) = c.month
			LEFT JOIN dt_user u on d.user_serial = u.user_serial
			LEFT JOIN dt_dep dep on dep.dep_serial = u.user_dep

			--移除不满足条件的人员

			delete d from  #data d
			inner join bd_kq_accounts c on d.user_serial = c.user_serial and SUBSTRING(d.year_month, 1, 4) = c.year and SUBSTRING(d.year_month, 6, 2) = c.month

			-- 更新用户姓名
			update a set a.user_name = isnull(b.user_lname,'')
			from #data a
			inner join dt_user b on a.user_serial = b.user_serial


			-- 插入考勤日志
			insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,gly_no,sj)
			select b.user_serial,date,1,6,2,0,0,@operator,getdate()
			from bd_sys_dim_date a
			inner join #data b on CONVERT(varchar(7),a.date,120) = b.year_month

			-- 插入操作日志
			insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
			select 13,0,concat(@operator,'删除',isnull(user_name,''),year_month,'排班'),@operator,getdate(),'',@ip,''
			from #data


			-- 删除明细表数据
			delete a
			from bd_kq_schedule_detail a
			inner join #data b on a.user_serial = b.user_serial and convert(varchar(7),a.shift_date,120) = b.year_month

			-- 删除排班表数据
			delete a
			from kt_paiba a
			inner join #data b on a.user_serial = b.user_serial and a.rq = b.year_month




			--创建一个游标
			declare my_cursor cursor for select user_serial, concat(year_month,'-',1), DATEADD(DAY,-(DATEPART(DAY,concat(year_month,'-',1))),DATEADD(MONTH,1,concat(year_month,'-',1))) from #data      --这是游标my_cursor的值，这里随便发挥看业务场景

			--打开游标
			open my_cursor                  --没什么好说的

				--变量
				declare @user_serial varchar(50);
				declare @begin_date varchar(50);
				declare @end_date varchar(50);

				--循环游标
				fetch next from my_cursor into @user_serial,@begin_date,@end_date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
				while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行

					-- 调用考勤计算(排班4)
					EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial,4

				fetch next from my_cursor into @user_serial,@begin_date,@end_date --获取下一条数据并赋值给变量

			end--关闭释放游标
			close my_cursor
			deallocate my_cursor


	--  提交事务
		COMMIT TRAN

--  结束异常捕捉
	END TRY

--  异常处理
	BEGIN CATCH
		DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
			RAISERROR (@errStr, 16, 1);
		END;
		ROLLBACK TRAN;
	END CATCH

	select * from  #return_table

END;
go
